package com.lavida.project.system.emp.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import com.lavida.project.system.emp.domain.Roster;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.lavida.project.system.emp.domain.Emp;

public interface EmpMapper {

	List<Emp> selectEmp(Emp emp);
	Emp selectEmpByempno(Integer job_id);
	
	
	@Delete("delete from emp where job_id=#{job_id}")
	int deleteEmpMesssage(Integer job_id);
	
	@Insert("insert into emp(job_id,name,company,fen_company,id_card) values(#{job_id},#{name},#{company},#{fen_company},#{id_card})")
	int insertEmpMessage(Roster roster);
	/**
	 * 获取状态为 '在职'， 生日距今天前10天的员工
	 * @return
	 */
	@Select("SELECT job_id,name,birthday,ABS(DATEDIFF(CURDATE(),CONCAT(YEAR(CURDATE()),'-',DATE_FORMAT(birthday,'%m'),'-',DATE_FORMAT(birthday,'%d')))) AS beforeDays FROM emp WHERE STATUS = '在职' or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东'  AND (DATEDIFF(CURDATE(),CONCAT(YEAR(CURDATE()),'-',DATE_FORMAT(birthday,'%m'),'-',DATE_FORMAT(birthday,'%d')))>=-9 AND DATEDIFF(CURDATE(),CONCAT(YEAR(CURDATE()),'-',DATE_FORMAT(birthday,'%m'),'-',DATE_FORMAT(birthday,'%d')))<1)")
	List<Emp> getCurrentEmpBirth();
	
	/**
	 * 获取状态为 '试用期' ，转正日期距今天前10天的员工
	 * @return
	 */
	@Select("SELECT job_id,name,empconfirmation,ABS(DATEDIFF(CURDATE(),empconfirmation)) AS beforeDays FROM emp WHERE STATUS = '试用期' AND (DATEDIFF(CURDATE(),empconfirmation)>=-9 AND DATEDIFF(CURDATE(),empconfirmation)<1)")
	List<Emp> getBecomeRegularEmp();
	
	/**
	 * 获取状态为 '在职'， 身份证有效期距今天前30天的员工
	 * @return
	 */
	@Select("SELECT job_id,name,carddate,ABS(DATEDIFF(CURDATE(),CONCAT(YEAR(CURDATE()),'-',DATE_FORMAT(carddate,'%m'),'-',DATE_FORMAT(carddate,'%d')))) AS beforeDays FROM emp WHERE STATUS = '在职' or status ='试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东'  AND (DATEDIFF(CURDATE(),CONCAT(YEAR(CURDATE()),'-',DATE_FORMAT(carddate,'%m'),'-',DATE_FORMAT(carddate,'%d')))>=-29 AND DATEDIFF(CURDATE(),CONCAT(YEAR(CURDATE()),'-',DATE_FORMAT(carddate,'%m'),'-',DATE_FORMAT(carddate,'%d')))<1)")
	List<Emp> getValidityCardEmp();
	
	/**
	 * 工作刚好满1年
	 * @return
	 */
	@Select("SELECT job_id,name,hiredate FROM emp WHERE STATUS!='离职' AND TIMESTAMPDIFF(YEAR, hiredate, CURDATE())=1 AND TIMESTAMPDIFF(YEAR, hiredate, DATE_SUB(CURDATE(), INTERVAL 1 DAY))= 0")
	List<Emp> getWorkOneYearEmp();
	
	/**
	 * 工作刚好满5年
	 * @return
	 */
	@Select("SELECT job_id,name,hiredate FROM emp WHERE STATUS!='离职'  AND TIMESTAMPDIFF(YEAR, DATE_SUB(hiredate, INTERVAL starthiredate MONTH), CURDATE())=5 AND TIMESTAMPDIFF(YEAR, DATE_SUB(hiredate, INTERVAL starthiredate MONTH), DATE_SUB(CURDATE(), INTERVAL 1 DAY))= 4")
	List<Emp> getWorkFiveYearEmp();
	
	/**
	 * 工作刚好满10年
	 * @return
	 */
	@Select("SELECT job_id,name,hiredate FROM emp WHERE STATUS!='离职'  AND TIMESTAMPDIFF(YEAR, DATE_SUB(hiredate, INTERVAL starthiredate MONTH), CURDATE())=10 AND TIMESTAMPDIFF(YEAR, DATE_SUB(hiredate, INTERVAL starthiredate MONTH), DATE_SUB(CURDATE(), INTERVAL 1 DAY))= 9")
	List<Emp> getWorkTenYearEmp();
	
	
	
	/**
	 * 获取本月/上个月 转正/调岗人数
	 * @param colName
	 * @param interval
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE YEAR(${colName}) = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL #{interval} MONTH), '%Y') AND MONTH(${colName}) =  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL #{interval} MONTH), '%m')")
	Integer selectIntervalMonthEmpNums(@Param("colName")String colName,@Param("interval")int interval);
	
	/**
	 * 获取在职人员各个学历的人数
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE STATUS = '在职'or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东' AND education = #{education}")
	public Integer getCurrentEducationEmpNum(String education);
	
	/**
	 * 获取在职人员年龄小于25的人数
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE STATUS = '在职'	or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东' AND YEAR(CURDATE())-YEAR(birthday)<=25")
	public Integer getCurrentAgeLTEqual25EmpNum();
	
	/**
	 * 获取在职人员年龄大于25且小于等于30的人数
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE STATUS = '在职' or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东' AND  YEAR(CURDATE())-YEAR(birthday)>25 AND YEAR(CURDATE())-YEAR(birthday)<=30")
	public Integer getCurrentAgeLTEqual30EmpNum();
	
	/**
	 * 获取在职人员年龄大于30且小于等于45的人数
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE STATUS = '在职' or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东' AND YEAR(CURDATE())-YEAR(birthday)>30 AND YEAR(CURDATE())-YEAR(birthday)<=45")
	public Integer getCurrentAgeLTEqual45EmpNum();
	
	/**
	 * 获取在职人员年龄大于45的人数
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE STATUS = '在职' or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东' AND YEAR(CURDATE())-YEAR(birthday)>45")
	public Integer getCurrentAgeGT45EmpNum();
	
	/**
	 * 获取在职人员男女性别的人数
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE STATUS = '在职' or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东' AND sex = #{sex}")
	public Integer getCurrentSexEmpNum(String sex);
	
	/**
	 * 获取在职某个国籍的人数
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE STATUS = '在职' or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东' AND nationality = #{nationality}")
	public Integer getCurrentNationEmpNum(String nationality);
	
	/**
	 * 获取在职不是某国籍的人数
	 * @return
	 */
	@Select("SELECT COUNT(job_id) FROM emp WHERE STATUS = '在职' or status = '试用期' or status = '实习期' OR status = '合同期'  OR status = '靠挂'  OR status = '退休回聘'  OR status = '无限期合同'  OR status = '外聘'  OR status= '股东' AND nationality != #{nationality}")
	public Integer getCurrentNotNationEmpNum(String nationality);
	
	@Select("select * from emp where job_id=#{job_id}")
	Emp selectByJob_id(Integer job_id);
	
	@Update("update emp set name=#{name},id_card=#{id_card},company=#{company},fen_company=#{fen_company} where job_id=#{job_id}")
	int updateEmpMessage(Roster roster);
	
	/**
	 * 更新员工信息模块
	 * @param emp
	 */
	int updateEmp(Emp emp);
}
